CREATE TABLE [dbo].[tb_RegistroContable]
(
[idRegistroContable] [int] NOT NULL IDENTITY(1, 1),
[Organizacion] [int] NOT NULL,
[Ejercicio] [int] NOT NULL CONSTRAINT [DF_tb_RegistroContable_Ejercicio] DEFAULT (datepart(year,getdate())),
[Fecha] [datetime] NOT NULL,
[Concepto] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TipoConcepto] [int] NULL,
[Finalidad] [int] NOT NULL,
[Proveedor] [int] NULL,
[FacturaODocumento] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SaldoAnterior] [float] NOT NULL CONSTRAINT [DF_tb_RegistroContable_SaldoAnterior] DEFAULT ((0)),
[Debe] [float] NOT NULL CONSTRAINT [DF_tb_RegistroContable_Debe] DEFAULT ((0)),
[Haber] [float] NOT NULL CONSTRAINT [DF_tb_RegistroContable_Haber] DEFAULT ((0)),
[Cuenta] [int] NULL,
[Imputacion] [int] NULL,
[Rendicion] [int] NULL,
[Balance] [int] NULL,
[Observaciones] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Expediente] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Resolucion] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FechaDeVencimiento] [datetime] NULL,
[OrgIntermedia] [int] NULL,
[SubsidioOAporte] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[actualizarSaldos] ON [dbo].[tb_RegistroContable]
FOR DELETE 
AS

declare @idCuenta int
declare @debe float
declare @haber float
declare @saldo float
declare @SaldoAnterior float

if update(debe) or update(haber)
begin

	declare cuentas cursor for Select idCuenta from tb_Cuentas where idCuenta = (Select cuenta from deleted)
	declare @idRegistroContable int
	open cuentas

	fetch next from cuentas into @idCuenta

	while @@fetch_status = 0
	begin
		Select @SaldoAnterior = 0
		declare registros cursor for Select debe, haber, saldoAnterior, idRegistroContable from tb_RegistroContable where Cuenta = @idCuenta order by fecha
		open registros
		fetch next from registros into @debe, @haber, @saldo, @idRegistroContable
		while @@fetch_status = 0
		begin
			update tb_RegistroContable set saldoAnterior = @saldoAnterior + @Haber - @Debe where idRegistroContable = @idRegistroContable
			Select @SaldoAnterior = @saldoAnterior + @Haber - @Debe
			fetch next from registros into @debe, @haber, @saldo, @idRegistroContable
		end
		close registros
		deallocate registros
		fetch next from cuentas into @idCuenta
	end

	close cuentas
	deallocate cuentas

end




GO
ALTER TABLE [dbo].[tb_RegistroContable] ADD CONSTRAINT [PK_tb_RegistroContable] PRIMARY KEY NONCLUSTERED  ([idRegistroContable]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_RegistroContable] WITH NOCHECK ADD CONSTRAINT [FK_tb_RegistroContable_tb_Balances] FOREIGN KEY ([Balance]) REFERENCES [dbo].[tb_Balances] ([idBalance])
GO
ALTER TABLE [dbo].[tb_RegistroContable] WITH NOCHECK ADD CONSTRAINT [FK_tb_RegistroContable_tb_Cuentas] FOREIGN KEY ([Cuenta]) REFERENCES [dbo].[tb_Cuentas] ([idCuenta])
GO
ALTER TABLE [dbo].[tb_RegistroContable] WITH NOCHECK ADD CONSTRAINT [FK_tb_RegistroContable_tb_FinalidadesDeGasto] FOREIGN KEY ([Finalidad]) REFERENCES [dbo].[tb_FinalidadesDeGasto] ([idFinalidadGasto])
GO
ALTER TABLE [dbo].[tb_RegistroContable] WITH NOCHECK ADD CONSTRAINT [FK_tb_RegistroContable_tb_Presupuesto] FOREIGN KEY ([Imputacion]) REFERENCES [dbo].[tb_Presupuesto] ([idPresupuesto])
GO
ALTER TABLE [dbo].[tb_RegistroContable] WITH NOCHECK ADD CONSTRAINT [FK_tb_RegistroContable_tb_Organizaciones] FOREIGN KEY ([Organizacion]) REFERENCES [dbo].[tb_Organizaciones] ([idOrganizaciones])
GO
ALTER TABLE [dbo].[tb_RegistroContable] WITH NOCHECK ADD CONSTRAINT [FK_tb_RegistroContable_tb_Organizaciones1] FOREIGN KEY ([OrgIntermedia]) REFERENCES [dbo].[tb_Organizaciones] ([idOrganizaciones])
GO
ALTER TABLE [dbo].[tb_RegistroContable] WITH NOCHECK ADD CONSTRAINT [FK_tb_RegistroContable_tb_Proveedores] FOREIGN KEY ([Proveedor]) REFERENCES [dbo].[tb_Proveedores] ([idProveedor])
GO
ALTER TABLE [dbo].[tb_RegistroContable] WITH NOCHECK ADD CONSTRAINT [FK_tb_RegistroContable_tb_Rendiciones] FOREIGN KEY ([Rendicion]) REFERENCES [dbo].[tb_Rendiciones] ([idRendicion])
GO
ALTER TABLE [dbo].[tb_RegistroContable] WITH NOCHECK ADD CONSTRAINT [FK_tb_RegistroContable_tb_TiposDeConceptos] FOREIGN KEY ([TipoConcepto]) REFERENCES [dbo].[tb_TiposDeConceptos] ([idTipoConcepto])
GO
ALTER TABLE [dbo].[tb_RegistroContable] NOCHECK CONSTRAINT [FK_tb_RegistroContable_tb_Balances]
GO
ALTER TABLE [dbo].[tb_RegistroContable] NOCHECK CONSTRAINT [FK_tb_RegistroContable_tb_Presupuesto]
GO
ALTER TABLE [dbo].[tb_RegistroContable] NOCHECK CONSTRAINT [FK_tb_RegistroContable_tb_Organizaciones]
GO
ALTER TABLE [dbo].[tb_RegistroContable] NOCHECK CONSTRAINT [FK_tb_RegistroContable_tb_Rendiciones]
GO
